using System.Collections.Generic;
using System.Linq;

using MvcCms.Data.Entities;
using Microsoft.Data.Extensions;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using Devart.Data.PostgreSql;

namespace MvcCms.Data.Code
{
    public class EntityMiscRepository : IMiscRepository
    {
        private readonly MvcCmsEntities _entities;  

        public EntityMiscRepository(IEntitiesProvider entitiesProvider)
        {
            _entities = entitiesProvider.GetEntityContext();
        }

        #region IMiscRepository Members

        public IEnumerable<StateProvince> ListStates()
        {
            return (from c in _entities.StateProvinceSet
                    where c.CountryRegionCode == "US"
                   orderby c.Name
                   select c).ToList();
        }

        public IEnumerable<Language> ListLanguages()
        {
            return (from languageList in _entities.LanguageSet
                   orderby languageList.LanguageName
                   select languageList).ToList();
        }
        public DTORowCount GetTableRowCount(string tableName, string socialMessageUserId, int portalId)
        {
            var command = _entities.CreateStoreCommand("get_table_row_count",
                                CommandType.StoredProcedure,
                                new PgSqlParameter("_PortalID", portalId),
                                new PgSqlParameter("_TableName", tableName.ToLower()),
                                new PgSqlParameter("_SocialMessageUserID", socialMessageUserId)                            
                                );
            using (command.Connection.CreateConnectionScope())
            {
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();
                    var rowCountObject = reader.GetValue(0);
                    var rowCount = int.Parse(rowCountObject.ToString());

                    var rowCountToReturn = new DTORowCount
                    {
                        RowCount = rowCount
                    };

                    return rowCountToReturn;
                }
            }
        }
        public Language GetLanguage(int langId)
        {
            return (from s in _entities.LanguageSet
                    where s.LanguageID == langId
                    select s).FirstOrDefault();
        }

        public Language EditLanguage(Language languageToEdit)
        {
            Language originalLanguage = GetLanguage(languageToEdit.LanguageID);

            // Save changes
            _entities.LanguageSet.ApplyCurrentValues(languageToEdit);
            _entities.SaveChanges();
            return languageToEdit;
        }

        public Language CreateLanguage(Language languageToCreate)
        {
            _entities.LanguageSet.AddObject(languageToCreate);
            _entities.SaveChanges();
            return languageToCreate;
        }
        public StateProvince GetStateProvince(string code)
        {
            return (from s in _entities.StateProvinceSet
                    where s.StateProvinceCode.ToLower() == code.ToLower()
                    select s).FirstOrDefault();
        }
        public StateProvince GetStateProvince(int stateId)
        {
            return (from s in _entities.StateProvinceSet
                    where s.StateProvinceID == stateId
                    select s).FirstOrDefault();
        }

        #endregion
    }
}